from flask import Flask, render_template, jsonify, request
import sqlite3

app = Flask(__name__, template_folder='templates')

def get_db_connection():
    conn = sqlite3.connect('shopify_posts.db')
    conn.row_factory = sqlite3.Row
    return conn

@app.route('/')
def index():
    return render_template('index.html')

@app.route('/get_data')
def get_data():
    try:
        id_param = request.args.get('id')
        state_param = request.args.get('state')
        
        conn = get_db_connection()
        cursor = conn.cursor()
        
        if id_param:
            cursor.execute('SELECT id, title_cn, content_cn, requirement, state FROM shopify_posts WHERE id = ?', (id_param,))
        elif state_param:
            cursor.execute('SELECT id, title_cn, content_cn, requirement, state FROM shopify_posts WHERE state = ?', (state_param,))
        else:
            cursor.execute('SELECT id, title_cn, content_cn, requirement, state FROM shopify_posts')
            
        rows = cursor.fetchall()
        data = [dict(row) for row in rows]
        conn.close()
        
        return jsonify({
            'data': data,
            'draw': request.args.get('draw', 1),
            'recordsTotal': len(data),
            'recordsFiltered': len(data)
        })
    except Exception as e:
        print(f"Error: {str(e)}")
        return jsonify({
            'error': str(e)
        }), 500

@app.route('/update_state', methods=['POST'])
def update_state():
    try:
        id = request.form.get('id')
        state = request.form.get('state')
        
        # 验证状态值
        if state not in ['1', '9']:
            return jsonify({
                'success': False,
                'error': '无效的状态值'
            }), 400
            
        conn = get_db_connection()
        cursor = conn.cursor()
        cursor.execute('UPDATE shopify_posts SET state = ? WHERE id = ?', 
                      (state, id))
        conn.commit()
        conn.close()
        return jsonify({'success': True})
    except Exception as e:
        print(f"Error: {str(e)}")
        return jsonify({
            'success': False,
            'error': str(e)
        }), 500

if __name__ == '__main__':
    app.run(debug=True)